#!/usr/bin/perl -w
use strict;

use FindBin;
use lib ("$FindBin::Bin/..", "/net/cpp-group/Leo/bin");
use db_parameters;
use ensembl_parameters;

use DBI;


print STDERR <<"HEADLINE";
888888888888888888888888888888888888888888888888888888888888888888888

    table_ens_id

	Description:
		Obtain ENSEMBL IDs from the database or from the sequence file
	Populates DB tables:
		ens_id except for the PI field

888888888888888888888888888888888888888888888888888888888888888888888

HEADLINE

sub retrieve_ensembl_ids($)
{
	my ($taxon) = @_;
	my $ens_db_name = $taxon->[ENS_TAXON] .'_core_'.$taxon->[ENS_VERSION];
	my $dbh = DBI->connect("dbi:mysql:$ens_db_name:$ens_db_host", "$ens_db_user", '',
							{
								RaiseError => 1,
								AutoCommit => 1,
								PrintError => 0
							}
						)
	or die "Database connection not made: DBI::errstr()";
	my $array = $dbh->selectall_arrayref(<<'PL/SQLCMD');
	SELECT
		ensg.stable_id,
		ensp.stable_id,
		enst.stable_id
	FROM
		translation_stable_id ensp, 
		translation ensp_t        , 
		transcript_stable_id enst , 
		transcript enst_t         , 
		gene_stable_id ensg       , 
		gene ensg_t                 
	WHERE
		ensp.translation_id = ensp_t.translation_id AND
		ensp_t.transcript_id = enst.transcript_id AND
		enst.transcript_id = enst_t.transcript_id AND
		enst_t.gene_id = ensg.gene_id AND
		ensg.gene_id = ensg_t.gene_id AND
		ensg.stable_id RLIKE '^ENS[A-Z]*G[0-9]+$' AND
		ensg.stable_id NOT LIKE '%EST%' ;
PL/SQLCMD
	return $array;

}

#
#	Get DB parameters
#
my @db_param = get_ensembl_taxon_data();
my $dbh = connect_to_panda();
$dbh->do("TRUNCATE public.ens_id;");
for my $taxon_data(@db_param)
{

	my $ids;

	#
	#	Retrieving IDs from Ensembl
	#
	{
		print STDERR "\tObtaining Ensemble IDs for ".$taxon_data->[TAXON]."\n";
		$ids = retrieve_ensembl_ids($taxon_data);
	}

	$dbh->do("DELETE from public.ens_id where species = '$taxon_data->[TAXON]'");
	print STDERR "\tCopying ".scalar(@$ids)." IDs to panda\n";
	$dbh->do("COPY public.ens_id(gene_id, prot_id,  transcript_id, species) FROM STDIN");
	for my $ens_id(@$ids)
	{
		$dbh->pg_putline("$ens_id->[0]\t$ens_id->[1]\t$ens_id->[2]\t$taxon_data->[TAXON]\n");
	}
	$dbh->pg_putline("\\.\n");
	$dbh->pg_endcopy;
}
print STDERR "\tCompleted\n";

$dbh->disconnect();
